﻿using BW.WMS.Application.Features.Location.Dtos;
using BW.WMS.Application.Features.Warehouse.Dtos;
using BW.WMS.Application.Features.Warehouse.Interfaces;
using BW.WMS.Core.Enums;
using BW.WMS.Core.Result;
using BW.WMS.Infrastructure.Data.Contexts;
using BW.WMS.Infrastructure.Services;
using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Yitter.IdGenerator;

namespace BW.WMS.Application.Features.Warehouse.Services
{
    /// <summary>
    /// WMS仓库管理—仓库服务实现层
    /// </summary>
    public class WMS_WarehouseService : IWMS_WarehouseService
    {
        private readonly DBContext dbContext;
        private readonly IRedisCacheService redisCacheService;
        private readonly Random _random = new Random();
        
        // 不同类型缓存的基础过期时间（分钟）
        private const int WarehouseListCacheMinutes = 15;    // 仓库列表缓存15分钟
        private const int WarehouseDetailCacheMinutes = 30;  // 仓库详情缓存30分钟
        private const int DepartmentTreeCacheMinutes = 60;   // 部门树缓存60分钟
        private const int ManagerListCacheMinutes = 45;      // 主管列表缓存45分钟
        
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="redisCacheService"></param>
        public WMS_WarehouseService(DBContext dbContext, IRedisCacheService redisCacheService)
        {
            this.dbContext = dbContext;
            this.redisCacheService = redisCacheService;
        }
        /// <summary>
        /// 获取仓库分页列表，支持多条件查询
        /// </summary>
        /// <param name="queryDto">查询条件DTO，包含关键字、状态、部门ID等筛选条件</param>
        /// <returns>仓库分页数据，包含总条数和当前页数据</returns>
        public async Task<ApiPage<WMS_WarehouseDto>> GetWarehousePageListAsync(WMS_WarehouseQueryDto queryDto)
        {
            //初始化返回结果对象
            var apiPage = new ApiPage<WMS_WarehouseDto>
            {
                Code = ApiEnums.Success,
                Message = "查询成功",
                PageData = new List<WMS_WarehouseDto>()
            };

            try
            {
                // 生成缓存键
                var cacheKey = GenerateWarehouseListCacheKey(queryDto);
                
                // 尝试从Redis缓存获取数据
                var cachedResult = await redisCacheService.GetAsync<ApiPage<WMS_WarehouseDto>>(cacheKey);
                if (cachedResult != null)
                {
                    // 命中缓存，直接返回
                    cachedResult.Message += " (来自缓存)";
                    return cachedResult;
                }

                // 缓存未命中，执行数据库查询
                // 计算分页偏移量
                int offset = (queryDto.PageIndex - 1) * queryDto.PageSize;

                // 构建查询条件
                var whereClause = new StringBuilder(" WHERE w.IsDeleted = 0");
                var parameters = new DynamicParameters();
                parameters.Add("@Offset", offset);
                parameters.Add("@PageSize", queryDto.PageSize);

                if (!string.IsNullOrWhiteSpace(queryDto.Keyword))
                {
                    whereClause.Append(" AND (w.WarehouseCode LIKE @Keyword OR w.WarehouseName LIKE @Keyword)");
                    parameters.Add("@Keyword", $"%{queryDto.Keyword}%");
                }

                if (queryDto.Status.HasValue)
                {
                    whereClause.Append(" AND w.Status = @Status");
                    parameters.Add("@Status", queryDto.Status);
                }

                if (queryDto.DepartmentId.HasValue)
                {
                    whereClause.Append(" AND w.DepartmentId = @DepartmentId");
                    parameters.Add("@DepartmentId", queryDto.DepartmentId);
                }

                if (queryDto.ManagerId.HasValue)
                {
                    whereClause.Append(" AND w.ManagerId = @ManagerId");
                    parameters.Add("@ManagerId", queryDto.ManagerId);
                }

                // 计算总数
                var countSql = $@"
                    SELECT COUNT(1) 
                    FROM WMS_Warehouse w 
                    {whereClause}";

                // 获取分页数据
                var pageSql = $@"
                    SELECT 
                        w.Id, w.WarehouseCode, w.WarehouseName, w.Capacity, w.Address, 
                        w.Phone, w.DepartmentId, d.DepartmentName, w.ManagerId, 
                        m.ManagerName, w.LocationCount, w.Status, w.Remark, w.CreateBy, w.CreateTime
                    FROM WMS_Warehouse w
                    LEFT JOIN WMS_Department d ON w.DepartmentId = d.Id AND d.IsDeleted = 0
                    LEFT JOIN WMS_Manager m ON w.ManagerId = m.Id AND m.IsDeleted = 0
                    {whereClause}
                    ORDER BY w.CreateTime DESC
                    LIMIT @Offset, @PageSize";

                // 获取数据库连接
                var connection = dbContext.GetConn();

                // 执行查询
                var warehouses = await connection.QueryAsync<WMS_WarehouseDto>(pageSql, parameters);
                var totalCount = await connection.ExecuteScalarAsync<int>(countSql, parameters);

                // 转换枚举值为名称
                foreach (var warehouse in warehouses)
                {
                    warehouse.StatusName = Enum.GetName(typeof(WarehouseStatusEnum), warehouse.Status);
                }

                // 设置返回数据
                apiPage.PageData = warehouses.ToList();
                apiPage.TodalCount = totalCount;

                // 将结果缓存到Redis，使用带随机因子的过期时间防止缓存雪崩
                var expiration = GetCacheExpiration(WarehouseListCacheMinutes);
                await redisCacheService.SetAsync(cacheKey, apiPage, expiration);
            }
            catch (Exception ex)
            {
                apiPage.Code = ApiEnums.Error;
                apiPage.Message = $"查询失败：{ex.Message}";
            }

            return apiPage;
        }
        /// <summary>
        /// 根据ID获取仓库详情，包括仓库基本信息和下属库位列表
        /// </summary>
        /// <param name="id">仓库ID</param>
        /// <returns>仓库详情DTO，包含基本信息和库位集合</returns>
        public async Task<ApiResult<WMS_WarehouseDto>> GetWarehouseByIdAsync(long id)
        {
            // 初始化返回结果对象
            var result = new ApiResult<WMS_WarehouseDto>
            {
                Code = ApiEnums.Success,
                Message = "查询成功"
            };

            try
            {
                // 生成缓存键
                var cacheKey = $"warehouse:detail:{id}";
                
                // 尝试从Redis缓存获取数据
                var cachedResult = await redisCacheService.GetAsync<ApiResult<WMS_WarehouseDto>>(cacheKey);
                if (cachedResult != null)
                {
                    // 命中缓存，直接返回
                    cachedResult.Message += " (来自缓存)";
                    return cachedResult;
                }

                // 缓存未命中，执行数据库查询
                // 获取数据库连接
                var connection = dbContext.GetConn();

                // 构建查询仓库信息的SQL，包括关联部门和主管信息
                var sql = @"
                    SELECT 
                        w.Id, w.WarehouseCode, w.WarehouseName, w.Capacity, w.Address, 
                        w.Phone, w.DepartmentId, d.DepartmentName, w.ManagerId, 
                        m.ManagerName, w.LocationCount, w.Status, w.Remark, w.CreateBy, w.CreateTime
                    FROM WMS_Warehouse w
                    LEFT JOIN WMS_Department d ON w.DepartmentId = d.Id AND d.IsDeleted = 0
                    LEFT JOIN WMS_Manager m ON w.ManagerId = m.Id AND m.IsDeleted = 0
                    WHERE w.Id = @Id AND w.IsDeleted = 0";

                // 创建查询参数
                var parameters = new DynamicParameters();
                parameters.Add("@Id", id);

                // 执行查询，获取仓库信息
                var warehouse = await connection.QueryFirstOrDefaultAsync<WMS_WarehouseDto>(sql, parameters);

                // 如果仓库不存在，返回失败信息
                if (warehouse == null)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "仓库不存在";
                    return result;
                }

                // 处理枚举值，转换为可读的状态名称
                warehouse.StatusName = Enum.GetName(typeof(WarehouseStatusEnum), warehouse.Status);

                // 构建查询该仓库下库位列表的SQL
                var locationSql = @"
                    SELECT 
                        l.Id, l.LocationCode, l.LocationName, l.Capacity, 
                        l.ManagerId, m.ManagerName, l.SortOrder, l.Status, 
                        l.Remark, l.WarehouseId, l.CreateBy, l.CreateTime
                    FROM WMS_Location l
                    LEFT JOIN WMS_Manager m ON l.ManagerId = m.Id AND m.IsDeleted = 0
                    WHERE l.WarehouseId = @WarehouseId AND l.IsDeleted = 0
                    ORDER BY l.SortOrder, l.CreateTime";

                // 创建查询参数
                var locationParams = new DynamicParameters();
                locationParams.Add("@WarehouseId", id);

                // 执行查询，获取库位列表
                var locations = await connection.QueryAsync<WMS_LocationDto>(locationSql, locationParams);

                // 处理库位状态枚举值，并设置所属仓库名称
                foreach (var location in locations)
                {
                    location.StatusName = Enum.GetName(typeof(LocationStatusEnum), location.Status);
                    location.WarehouseName = warehouse.WarehouseName;
                }

                // 将库位列表添加到仓库对象中
                warehouse.Locations = locations.ToList();
                result.Result = warehouse;

                // 将结果缓存到Redis，使用带随机因子的过期时间防止缓存雪崩
                var expiration = GetCacheExpiration(WarehouseDetailCacheMinutes);
                await redisCacheService.SetAsync(cacheKey, result, expiration);
            }
            catch (Exception ex)
            {
                // 异常处理，记录错误信息并返回给前端
                result.Code = ApiEnums.Error;
                result.Message = $"查询失败：{ex.Message}";
            }

            return result;
        }
        /// <summary>
        /// 创建新仓库
        /// </summary>
        /// <param name="createDto">仓库创建DTO，包含仓库的基本信息</param>
        /// <returns>操作结果，成功时返回新创建的仓库ID</returns>
        public async Task<ApiResult<long>> CreateWarehouseAsync(WMS_WarehouseCreateDto createDto)
        {
            var result = new ApiResult<long>
            {
                Code = ApiEnums.Success,
                Message = "创建成功"
            };

            try
            {
                // 获取数据库连接
                var connection = dbContext.GetConn();

                // 检查仓库编号是否已存在
                var existsSql = "SELECT COUNT(1) FROM WMS_Warehouse WHERE WarehouseCode = @WarehouseCode AND IsDeleted = 0";
                var existsParams = new DynamicParameters();
                existsParams.Add("@WarehouseCode", createDto.WarehouseCode);

                var exists = await connection.ExecuteScalarAsync<int>(existsSql, existsParams);

                if (exists > 0)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "仓库编号已存在";
                    result.Result = 0;
                    return result;
                }

                // 检查部门ID是否存在
                var departmentExistsSql = "SELECT COUNT(1) FROM WMS_Department WHERE Id = @DepartmentId AND IsDeleted = 0";
                var departmentParams = new DynamicParameters();
                departmentParams.Add("@DepartmentId", createDto.DepartmentId);

                var departmentExists = await connection.ExecuteScalarAsync<int>(departmentExistsSql, departmentParams);

                if (departmentExists == 0)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "所选部门不存在，请选择有效的部门";
                    result.Result = 0;
                    return result;
                }

                // 检查主管ID是否存在
                var managerExistsSql = "SELECT COUNT(1) FROM WMS_Manager WHERE Id = @ManagerId AND IsDeleted = 0";
                var managerParams = new DynamicParameters();
                managerParams.Add("@ManagerId", createDto.ManagerId);

                var managerExists = await connection.ExecuteScalarAsync<int>(managerExistsSql, managerParams);

                if (managerExists == 0)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "所选主管不存在，请选择有效的主管";
                    result.Result = 0;
                    return result;
                }

                // 生成雪花ID
                long id = YitIdHelper.NextId();

                // 构建插入仓库数据的SQL，使用雪花ID
                var sql = @"
            INSERT INTO WMS_Warehouse (
                Id, WarehouseCode, WarehouseName, Capacity, Address, 
                Phone, DepartmentId, ManagerId, LocationCount, 
                Status, Remark, CreateBy, CreateTime, UpdateBy, UpdateTime, IsDeleted
            ) VALUES (
                @Id, @WarehouseCode, @WarehouseName, @Capacity, @Address, 
                @Phone, @DepartmentId, @ManagerId, @LocationCount, 
                @Status, @Remark, @CreateBy, NOW(), @CreateBy, NOW(), 0
            );";

                // 创建SQL参数
                var parameters = new DynamicParameters();
                parameters.Add("@Id", id);
                parameters.Add("@WarehouseCode", createDto.WarehouseCode);
                parameters.Add("@WarehouseName", createDto.WarehouseName);
                parameters.Add("@Capacity", createDto.Capacity);
                parameters.Add("@Address", createDto.Address);
                parameters.Add("@Phone", createDto.Phone);
                parameters.Add("@DepartmentId", createDto.DepartmentId);
                parameters.Add("@ManagerId", createDto.ManagerId);
                parameters.Add("@LocationCount", createDto.LocationCount);
                parameters.Add("@Status", createDto.Status);
                parameters.Add("@Remark", createDto.Remark);
                parameters.Add("@CreateBy", createDto.CreateBy);
                // 执行SQL并设置返回的ID
                await connection.ExecuteAsync(sql, parameters);
                result.Result = id;

                // 清理相关缓存
                await ClearWarehouseCacheAsync();
            }
            catch (Exception ex)
            {
                result.Code = ApiEnums.Error;
                result.Message = $"创建失败：{ex.Message}";
                result.Result = 0;
            }

            return result;
        }
        /// <summary>
        /// 更新仓库信息
        /// </summary>
        /// <param name="updateDto">仓库更新DTO，包含需要更新的字段</param>
        /// <returns>操作结果，表示更新是否成功</returns>
        public async Task<ApiResult<bool>> UpdateWarehouseAsync(WMS_WarehouseUpdateDto updateDto)
        {
            // 初始化返回结果对象
            var result = new ApiResult<bool>
            {
                Code = ApiEnums.Success,
                Message = "更新成功",
                Result = true
            };

            try
            {
                // 获取数据库连接
                var connection = dbContext.GetConn();

                // 检查仓库是否存在
                var existsSql = "SELECT COUNT(1) FROM WMS_Warehouse WHERE Id = @Id AND IsDeleted = 0";
                var existsParams = new DynamicParameters();
                existsParams.Add("@Id", updateDto.Id);

                var exists = await connection.ExecuteScalarAsync<int>(existsSql, existsParams);

                // 如果仓库不存在，返回失败信息
                if (exists == 0)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "仓库不存在";
                    result.Result = false;
                    return result;
                }

                // 构建更新仓库数据的SQL
                var sql = @"
                    UPDATE WMS_Warehouse SET
                        WarehouseName = @WarehouseName,
                        Capacity = @Capacity,
                        Address = @Address,
                        Phone = @Phone,
                        DepartmentId = @DepartmentId,
                        ManagerId = @ManagerId,
                        Status = @Status,
                        Remark = @Remark,
                        UpdateBy = @UpdateBy,
                        UpdateTime = NOW()
                    WHERE Id = @Id";

                // 创建SQL参数
                var parameters = new DynamicParameters();
                parameters.Add("@Id", updateDto.Id);
                parameters.Add("@WarehouseName", updateDto.WarehouseName);
                parameters.Add("@Capacity", updateDto.Capacity);
                parameters.Add("@Address", updateDto.Address);
                parameters.Add("@Phone", updateDto.Phone);
                parameters.Add("@DepartmentId", updateDto.DepartmentId);
                parameters.Add("@ManagerId", updateDto.ManagerId);
                parameters.Add("@Status", updateDto.Status);
                parameters.Add("@Remark", updateDto.Remark);
                parameters.Add("@UpdateBy", updateDto.UpdateBy);

                // 执行更新操作
                var affected = await connection.ExecuteAsync(sql, parameters);
                result.Result = affected > 0; // 判断是否有记录被更新

                if (result.Result)
                {
                    // 清理相关缓存
                    await ClearWarehouseCacheAsync();
                }
            }
            catch (Exception ex)
            {
                // 异常处理，记录错误信息并返回给前端
                result.Code = ApiEnums.Error;
                result.Message = $"更新失败：{ex.Message}";
                result.Result = false;
            }

            return result;
        }
        /// <summary>
        /// 删除仓库（逻辑删除）
        /// </summary>
        /// <param name="id">仓库ID</param>
        /// <returns>操作结果，表示删除是否成功</returns>
        public async Task<ApiResult<bool>> DeleteWarehouseAsync(long id)
        {
            // 初始化返回结果对象
            var result = new ApiResult<bool>
            {
                Code = ApiEnums.Success,
                Message = "删除成功",
                Result = true
            };

            try
            {
                // 获取数据库连接
                var connection = dbContext.GetConn();

                // 检查仓库是否存在
                var existsSql = "SELECT COUNT(1) FROM WMS_Warehouse WHERE Id = @Id AND IsDeleted = 0";
                var existsParams = new DynamicParameters();
                existsParams.Add("@Id", id);

                var exists = await connection.ExecuteScalarAsync<int>(existsSql, existsParams);

                // 如果仓库不存在，返回失败信息
                if (exists == 0)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "仓库不存在";
                    result.Result = false;
                    return result;
                }

                // 检查是否有库位关联到该仓库，如果有则不允许删除
                var checkLocationSql = "SELECT COUNT(1) FROM WMS_Location WHERE WarehouseId = @Id AND IsDeleted = 0";
                var checkParams = new DynamicParameters();
                checkParams.Add("@Id", id);

                var hasLocations = await connection.ExecuteScalarAsync<int>(checkLocationSql, checkParams);

                if (hasLocations > 0)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "该仓库下存在库位，不能删除";
                    result.Result = false;
                    return result;
                }

                // 构建逻辑删除仓库的SQL（设置IsDeleted标志）
                var sql = @"
            UPDATE WMS_Warehouse SET
                IsDeleted = 1,
                UpdateTime = NOW()
            WHERE Id = @Id";

                // 创建SQL参数
                var parameters = new DynamicParameters();
                parameters.Add("@Id", id);

                // 执行删除操作
                var affected = await connection.ExecuteAsync(sql, parameters);
                result.Result = affected > 0; // 判断是否有记录被更新

                if (result.Result)
                {
                    // 清理相关缓存
                    await ClearWarehouseCacheAsync();
                }
            }
            catch (Exception ex)
            {
                // 异常处理，记录错误信息并返回给前端
                result.Code = ApiEnums.Error;
                result.Message = $"删除失败：{ex.Message}";
                result.Result = false;
            }

            return result;
        }

        /// <summary>
        /// 批量删除仓库（逻辑删除）
        /// </summary>
        /// <param name="ids">仓库ID列表</param>
        /// <returns>操作结果，表示删除是否成功</returns>
        public async Task<ApiResult<bool>> BatchDeleteWarehousesAsync(List<long> ids)
        {
            // 初始化返回结果对象
            var result = new ApiResult<bool>
            {
                Code = ApiEnums.Success,
                Message = "批量删除成功",
                Result = true
            };

            try
            {
                // 获取数据库连接
                var connection = dbContext.GetConn();

                // 检查是否有库位关联到这些仓库，如果有则不允许删除
                var checkLocationSql = "SELECT COUNT(1) FROM WMS_Location WHERE WarehouseId IN @Ids AND IsDeleted = 0";
                var checkParams = new DynamicParameters();
                checkParams.Add("@Ids", ids);

                var hasLocations = await connection.ExecuteScalarAsync<int>(checkLocationSql, checkParams);

                if (hasLocations > 0)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "选中的仓库中存在关联的库位，不能删除";
                    result.Result = false;
                    return result;
                }

                // 构建批量逻辑删除仓库的SQL
                var sql = @"
            UPDATE WMS_Warehouse SET
                IsDeleted = 1,
                UpdateTime = NOW()
            WHERE Id IN @Ids";

                // 创建SQL参数
                var parameters = new DynamicParameters();
                parameters.Add("@Ids", ids);

                // 执行批量删除操作
                var affected = await connection.ExecuteAsync(sql, parameters);
                result.Result = affected > 0; // 判断是否有记录被更新

                if (result.Result)
                {
                    // 清理相关缓存
                    await ClearWarehouseCacheAsync();
                }
            }
            catch (Exception ex)
            {
                // 异常处理，记录错误信息并返回给前端
                result.Code = ApiEnums.Error;
                result.Message = $"批量删除失败：{ex.Message}";
                result.Result = false;
            }

            return result;
        }
        /// <summary>
        /// 更新仓库状态
        /// </summary>
        /// <param name="id">仓库ID</param>
        /// <param name="status">新状态值</param>
        /// <param name="updatedBy">操作人</param>
        /// <returns>操作结果，表示状态更新是否成功</returns>
        public async Task<ApiResult<bool>> UpdateWarehouseStatusAsync(long id, int status, string updatedBy)
        {
            // 初始化返回结果对象
            var result = new ApiResult<bool>
            {
                Code = ApiEnums.Success,
                Message = "状态更新成功",
                Result = true
            };

            try
            {
                // 获取数据库连接
                var connection = dbContext.GetConn();

                // 检查仓库是否存在
                var existsSql = "SELECT COUNT(1) FROM WMS_Warehouse WHERE Id = @Id AND IsDeleted = 0";
                var existsParams = new DynamicParameters();
                existsParams.Add("@Id", id);

                var exists = await connection.ExecuteScalarAsync<int>(existsSql, existsParams);

                // 如果仓库不存在，返回失败信息
                if (exists == 0)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "仓库不存在";
                    result.Result = false;
                    return result;
                }

                // 检查状态值是否在枚举范围内
                if (!Enum.IsDefined(typeof(WarehouseStatusEnum), status))
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "无效的状态值";
                    result.Result = false;
                    return result;
                }

                // 构建更新仓库状态的SQL
                var sql = @"
                    UPDATE WMS_Warehouse SET
                        Status = @Status,
                        Operator = @UpdatedBy,
                        OperationTime = NOW(),
                        UpdateBy = @UpdatedBy,
                        UpdateTime = NOW()
                    WHERE Id = @Id";

                // 创建SQL参数
                var parameters = new DynamicParameters();
                parameters.Add("@Id", id);
                parameters.Add("@Status", status);
                parameters.Add("@UpdatedBy", updatedBy);

                // 执行更新操作
                var affected = await connection.ExecuteAsync(sql, parameters);
                result.Result = affected > 0; // 判断是否有记录被更新

                if (result.Result)
                {
                    // 清理相关缓存
                    await ClearWarehouseCacheAsync();
                }
            }
            catch (Exception ex)
            {
                // 异常处理，记录错误信息并返回给前端
                result.Code = ApiEnums.Error;
                result.Message = $"状态更新失败：{ex.Message}";
                result.Result = false;
            }

            return result;
        }

        /// <summary>
        /// 获取所有部门列表（树形结构），用于下拉选择框
        /// </summary>
        /// <param name="parentId">父级部门ID，默认为0表示获取顶级部门</param>
        /// <returns>部门树形结构数据</returns>
        public async Task<ApiResult<List<DepartmentTreeDto>>> GetAllDepartmentsTreeAsync(long parentId = 0)
        {
            // 初始化返回结果对象
            var result = new ApiResult<List<DepartmentTreeDto>>
            {
                Code = ApiEnums.Success,
                Message = "查询成功",
                Result = new List<DepartmentTreeDto>()
            };

            try
            {
                // 生成缓存键
                var cacheKey = $"warehouse:departments:tree:{parentId}";
                
                // 尝试从Redis缓存获取数据
                var cachedResult = await redisCacheService.GetAsync<ApiResult<List<DepartmentTreeDto>>>(cacheKey);
                if (cachedResult != null)
                {
                    // 命中缓存，直接返回
                    cachedResult.Message += " (来自缓存)";
                    return cachedResult;
                }

                // 缓存未命中，执行数据库查询
                // 获取数据库连接
                var connection = dbContext.GetConn();

                // 构建查询部门列表的SQL，支持树形结构
                var sql = @"
            SELECT 
                Id, 
                DepartmentName, 
                ParentId, 
                CreateTime
            FROM WMS_Department
            WHERE IsDeleted = 0
            ORDER BY DepartmentName";

                // 执行查询
                var departments = await connection.QueryAsync<DepartmentDto>(sql);

                // 构建树形结构
                result.Result = BuildDepartmentTree(departments.ToList(), parentId);

                // 将结果缓存到Redis，使用带随机因子的过期时间防止缓存雪崩
                var expiration = GetCacheExpiration(DepartmentTreeCacheMinutes);
                await redisCacheService.SetAsync(cacheKey, result, expiration);
            }
            catch (Exception ex)
            {
                // 异常处理，记录错误信息并返回给前端
                result.Code = ApiEnums.Error;
                result.Message = $"查询失败：{ex.Message}";
            }

            return result;
        }

        /// <summary>
        /// 获取所有仓库主管列表，用于下拉选择框
        /// </summary>
        /// <param name="departmentId">部门ID，可选参数，用于筛选特定部门的主管</param>
        /// <returns>主管ID和名称的键值对列表</returns>
        public async Task<ApiResult<List<KeyValuePair<long, string>>>> GetAllManagersAsync(long? departmentId = null)
        {
            // 初始化返回结果对象
            var result = new ApiResult<List<KeyValuePair<long, string>>>
            {
                Code = ApiEnums.Success,
                Message = "查询成功",
                Result = new List<KeyValuePair<long, string>>()
            };

            try
            {
                // 生成缓存键
                var cacheKey = $"warehouse:managers:list:{departmentId ?? 0}";
                
                // 尝试从Redis缓存获取数据
                var cachedResult = await redisCacheService.GetAsync<ApiResult<List<KeyValuePair<long, string>>>>(cacheKey);
                if (cachedResult != null)
                {
                    // 命中缓存，直接返回
                    cachedResult.Message += " (来自缓存)";
                    return cachedResult;
                }

                // 缓存未命中，执行数据库查询
                // 获取数据库连接
                var connection = dbContext.GetConn();

                string sql;
                DynamicParameters parameters = null;

                // 根据是否提供部门ID构建不同的SQL
                if (departmentId.HasValue)
                {
                    // 查询指定部门的主管
                    sql = @"
                SELECT Id, ManagerName
                FROM WMS_Manager
                WHERE IsDeleted = 0 AND DepartmentId = @DepartmentId
                ORDER BY ManagerName";

                    parameters = new DynamicParameters();
                    parameters.Add("@DepartmentId", departmentId.Value);
                }
                else
                {
                    // 查询所有主管
                    sql = @"
                SELECT Id, ManagerName
                FROM WMS_Manager
                WHERE IsDeleted = 0
                ORDER BY ManagerName";
                }

                // 执行查询
                var managers = await connection.QueryAsync<dynamic>(sql, parameters);

                // 将查询结果转换为键值对列表
                result.Result = managers.Select(m =>
                    new KeyValuePair<long, string>(m.Id, m.ManagerName)).ToList();

                // 将结果缓存到Redis，使用带随机因子的过期时间防止缓存雪崩
                var expiration = GetCacheExpiration(ManagerListCacheMinutes);
                await redisCacheService.SetAsync(cacheKey, result, expiration);
            }
            catch (Exception ex)
            {
                // 异常处理，记录错误信息并返回给前端
                result.Code = ApiEnums.Error;
                result.Message = $"查询失败：{ex.Message}";
            }

            return result;
        }

        /// <summary>
        /// 构建部门树形结构的私有方法
        /// </summary>
        /// <param name="departments">所有部门列表</param>
        /// <param name="parentId">父级部门ID</param>
        /// <returns>树形结构的部门列表</returns>
        private List<DepartmentTreeDto> BuildDepartmentTree(List<DepartmentDto> departments, long parentId)
        {
            var tree = new List<DepartmentTreeDto>();

            // 查找指定父级ID下的所有部门
            var children = departments.Where(d => d.ParentId == parentId).ToList();

            foreach (var child in children)
            {
                var treeNode = new DepartmentTreeDto
                {
                    Id = child.Id,
                    DepartmentName = child.DepartmentName,
                    ParentId = child.ParentId,
                    CreateTime = child.CreateTime,
                    Children = new List<DepartmentTreeDto>()
                };

                // 递归构建子部门
                treeNode.Children = BuildDepartmentTree(departments, child.Id);
                tree.Add(treeNode);
            }

            return tree;
        }

        /// <summary>
        /// 生成带随机因子的缓存过期时间，防止缓存雪崩
        /// </summary>
        /// <param name="baseMinutes">基础过期时间（分钟）</param>
        /// <param name="randomRangeMinutes">随机范围（分钟），默认为基础时间的20%</param>
        /// <returns>包含随机因子的过期时间</returns>
        private TimeSpan GetCacheExpiration(int baseMinutes, int? randomRangeMinutes = null)
        {
            var randomRange = randomRangeMinutes ?? (int)(baseMinutes * 0.2); // 默认20%的随机范围
            var randomOffset = _random.Next(-randomRange, randomRange + 1);
            var totalMinutes = Math.Max(1, baseMinutes + randomOffset); // 确保至少1分钟
            return TimeSpan.FromMinutes(totalMinutes);
        }

        /// <summary>
        /// 生成仓库列表缓存键
        /// </summary>
        /// <param name="queryDto">查询条件</param>
        /// <returns>缓存键</returns>
        private string GenerateWarehouseListCacheKey(WMS_WarehouseQueryDto queryDto)
        {
            var keyBuilder = new StringBuilder("warehouse:list:");
            keyBuilder.Append($"page:{queryDto.PageIndex}:");
            keyBuilder.Append($"size:{queryDto.PageSize}:");
            
            if (!string.IsNullOrWhiteSpace(queryDto.Keyword))
                keyBuilder.Append($"keyword:{queryDto.Keyword}:");
            
            if (queryDto.Status.HasValue)
                keyBuilder.Append($"status:{queryDto.Status}:");
                
            if (queryDto.DepartmentId.HasValue)
                keyBuilder.Append($"dept:{queryDto.DepartmentId}:");
                
            if (queryDto.ManagerId.HasValue)
                keyBuilder.Append($"mgr:{queryDto.ManagerId}:");

            return keyBuilder.ToString();
        }

        /// <summary>
        /// 清理仓库相关缓存
        /// </summary>
        /// <returns></returns>
        private async Task ClearWarehouseCacheAsync()
        {
            var patterns = new[]
            {
                "warehouse:list:*",
                "warehouse:detail:*",
                "warehouse:departments:*",
                "warehouse:managers:*"
            };

            foreach (var pattern in patterns)
            {
                // 注意：Redis集群模式不支持通配符删除，这里采用简化处理
                // 在实际生产环境中，可以考虑使用Redis Scan命令或者设置较短的过期时间
                await redisCacheService.DeleteAsync(pattern);
            }
        }
    }
}
